<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2021/1/7
 * Time: 22:11
 */
namespace App\Service;

use App\Model\Dao\SysExportLogDao;
use App\Utils\CustomUtils;
use Hyperf\Di\Annotation\Inject;
use Hyperf\Utils\ApplicationContext;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class OfficeService
{

    private $spreadsheet = NULL;

    /**
     * @Inject()
     * @var SysExportLogDao
     */
    private $SysExportLogDao;

    public function __construct()
    {
        $this->spreadsheet = new Spreadsheet();
    }

    /**
     *
     * 导出excel文件
     *  调用示例：
     *  $OfficeService = new OfficeService();
     *  $fileName      = 'test'.date('Y-m-d-H-i-s');
     *  $xlsCell       = [
     *      [
     *          'filed' => 'id',
     *          'name'  => '序号',
     *      ],
     *      [
     *          'filed' => 'title',
     *          'name'  => '标题',
     *      ],
     *  ];
     *  $xlsData       = [
     *      [
     *          'id'    => 1,
     *          'title' => '测试标题',
     *      ],
     *      [
     *          'id'    => 2,
     *          'title' => '测试标题',
     *      ],
     *  ];
     *  $log = ExportOfficeLog::create([
     *      'filename'    => $fileName,
     *      'entityClass' => ExportOfficeLog::class,
     *      'adminId'     => 1,
     *  ]);
     *  $OfficeService->exportExcel($fileName, $xlsCell, $xlsData, $log->getId());
     *
     * @param string $fileName          文件名，不可为中文,不带后缀名
     * @param array  $xlsCell           表头数据，二维数组，格式：$xlsCell = [ [ 'filed'=>'id','name'=>'序号'],[ 'filed'=>'title','name'=>'标题']  ]
     * @param array  $xlsData           导出数据，二维数组，可直接传数据库查询数据
     * @param string $logId             export_log表自增Id,传入则更新对应表记录
     * @param int    $setTimeLimit      程序可执行时间，0为不超时，异步导出建议设置为0
     * @param int    $defaultCellWidth  默认列宽
     * @param int    $defaultLineHeight 默认行高
     *
     * @return string $savePath 文件保存绝对路径
     * @throws \ReflectionException
     */
    public function exportExcel(string $fileName, array $xlsCell, array $xlsData, string $logId = '', int $setTimeLimit = 0, int $defaultCellWidth = 20, int $defaultLineHeight = 20)
    {

        $exportLog = $this->SysExportLogDao->get($logId);
        $savePath  = '';
        $filePath = '';
        try
        {
            // 执行时间默认不超时
            set_time_limit($setTimeLimit);

//            $sheet = $this->spreadsheet->getActiveSheet();
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();

            // 解决文件损坏
            if (ob_get_length() > 0)
            {
                ob_clean();
            }

            $saveDir = env('EXPORT_SAVE_PATH');
            if (!is_dir($saveDir))
            {
                mkdir($saveDir, 0755);
            }
            $date = date('Ymd');
            if (!is_dir($saveDir . '/' . $date))
            {
                mkdir($saveDir . '/' . $date, 0755);
            }

            $saveDir .= '/' . $date . '/';
            $fileDir = env('EXPORT_FILE_PATH') . '/' . $date . '/';

            // 文件名称
            $fileName = iconv('utf-8', 'gb2312', $fileName);

            $cellName = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'];


            // 默认行高
            $sheet->getDefaultRowDimension()->setRowHeight($defaultLineHeight);

            // 设置表头
            foreach ($xlsCell as $key => $cell)
            {
                $sheet->setCellValue($cellName[ $key ] . '1', $cell['name']);
                // 固定列宽，表格宽度
                $sheet->getColumnDimension($cellName[ $key ])->setWidth($defaultCellWidth);
            }

            // 数据渲染
            foreach ($xlsData as $key => $item)
            {
                foreach ($xlsCell as $k => $cell)
                {
                    $sheet->setCellValue($cellName[ $k ] . ($key + 2), $item[ $cell['filed'] ]);
                    // 固定列宽，表格宽度
                    $sheet->getColumnDimension($cellName[ $k ])->setWidth($defaultCellWidth);
                }
            }

            $fileName .=  '.xlsx';
            $savePath = $saveDir . $fileName;
            $filePath = $fileDir . $fileName;

            // 保存文件
//            $writer   = new Xlsx($this->spreadsheet);
            $writer   = new Xlsx($spreadsheet);
            $writer->save($savePath);

            // 更新导出日志
            if ($exportLog) {
                $updateData = [
                    'status'    => 1,
                    'filePath'  => $savePath,
                    'fileName'  => $fileName
                ];
                $this->SysExportLogDao->update($logId, $updateData);
            }

            // 释放内存
//            $this->spreadsheet->disconnectWorksheets();
            $spreadsheet->disconnectWorksheets();

        } catch (\Exception $e) {

            CustomUtils::xzxDebug("生成xlsx异常,{$e->getMessage()}");
            // 更新导出日志
            if ($exportLog) {
                $updateData = [
                    'status'    => 2,
                    'remark'    => $e->getMessage()
                ];
                $this->SysExportLogDao->update($logId, $updateData);
            }
        }

        return $filePath;
    }

}